using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Budgeteer.Services.Domain;

namespace Budgeteer.Services.DataProviders
{
    public interface ICategoryDataProvider
    {
        IList<CategoryDto> GetCategoriesAmount(DateTime startDate, DateTime endDate);
        IDictionary<string, string> GetAllCategoryMaskPairs();
    }

    public class CategoryDataProvider : BaseDataProvider, ICategoryDataProvider
    {
        public CategoryDataProvider(string connectionString)
            : base(connectionString)
        {}

        public virtual IList<CategoryDto> GetCategoriesAmount(DateTime startDate, DateTime endDate)
        {
            using(SqlConnection conn = CreateConnection())
            {
                string sql = @"
                    SELECT CAT.Name As Category, SUM(CHG.Amount) AS TotalAmount
                    FROM Charge CHG
                        INNER JOIN Category CAT ON (CHG.CategoryId = CAT.Id)
                    WHERE CHG.Time > @StartDate and CHG.Time < @EndDate
                    GROUP BY CAT.Name";

                using(SqlCommand command = CreateCommand(sql,conn))
                {
                    SetParameter(command, "@StartDate", startDate);
                    SetParameter(command, "@EndDate", endDate);

                    using(SqlDataReader reader = command.ExecuteReader())
                    {
                        IList<CategoryDto> categories = new List<CategoryDto>();
                        while(reader != null && reader.Read())
                        {
                            categories.Add(ConvertReaderDataIntoCategory(reader));
                        }
                        return categories;

                    }
                }
            }
        }

        public virtual IDictionary<string,string> GetAllCategoryMaskPairs()
        {
            using(SqlConnection conn = CreateConnection())
            {
                string sql = @"
                    SELECT Name, Mask
                    FROM Category
                ";

                using(SqlCommand command = CreateCommand(sql,conn))
                {
                    using(SqlDataReader reader = command.ExecuteReader())
                    {
                        IDictionary<string,string> categoryMasks = new Dictionary<string,string>();
                        while(reader != null && reader.Read())
                        {
                            categoryMasks.Add(Convert.ToString(reader["Name"]),
                                Convert.ToString(reader["Mask"]));
                        }
                        return categoryMasks;

                    }
                }
            }
        }

        static CategoryDto ConvertReaderDataIntoCategory(IDataRecord reader)
        {
            return new CategoryDto
            {
                Name = Convert.ToString(reader["Category"]),
                TotalAmount = Convert.ToDecimal(reader["TotalAmount"]),
            };
        }
    }
}